const mydb = require("../config/mysqlConfig");
const time = require("../config/Time");
const xlsx = require("node-xlsx");
var uuid = require("node-uuid");
class FontionModule {
  // 登录
  async login(req, res, next) {
    try {
      const { name, password } = req.body;
      const sql = `select name from user where name= '${name}' and password = '${password}'`;
      const data = await mydb(sql);
      res.send({
        data: data,
      });
    } catch (error) {
      console.log(error);
    }
  }
  // 文件的导入
  async importFile(req, res, next) {
    const { PA, BC, ItemNumber, TaiShu } = req.body;
    var uuids = uuid.v1();
    try {
      let file = req.files;
      const obj = xlsx.parse(`./${file[0].path}`);
      /* 是需要用到的解析数据 */
      let newdata = obj[0].data;
      // 创建一个新的数组来装改造后的JSON对象
      var newarr = [];
      var lastarry = [];
      newdata.splice(0, 2);
      newdata.splice(1, 1);
      newdata.splice(1, 1);
      newdata.splice(1, 1);
      for (let i = 0; i < 6; i++) {
        newdata.pop();
      }
      for (let i = 0; i < newdata.length; i++) {
        if (newdata[i].length < 5) {
          newdata.splice(i, i);
        }
      }
      newdata[0][0] = "SerialNumber";
      newdata[0][1] = "Keymaterial";
      newdata[0][2] = "MaterialType";
      newdata[0][3] = "MaterialCode";
      newdata[0][4] = "MaterialName";
      newdata[0][5] = "SpecificationsModels";
      newdata[0][6] = "ParameterDescription";
      newdata[0][7] = "Brand";
      newdata[0][8] = "Link";
      newdata[0][9] = "FunctionTest";
      newdata[0][10] = "Manufacturer";
      newdata[0][11] = "Reference";
      newdata[0][12] = "null";
      newdata[0][13] = "null";
      newdata[0][14] = "null";
      newdata[0][15] = "AmountUsed";
      newdata[0][16] = "FixedLoss";
      newdata[0][17] = "Unit";
      newdata[0][18] = "Remark";
      newdata[0][19] = "Materialleibie";
      newdata[0][20] = "delete";
      for (let i = 1; i < newdata.length; i++) {
        // 设置一个空对象装改造后需要的属性
        let newobj = {};
        // 循环放新对象需要的属性
        for (let j = 0; j < newdata[0].length; j++) {
          // 属性名
          let jname = newdata[0][j];
          newobj[jname] = newdata[i][j];
        }
        newarr.push(newobj);
      }

      for (let i = 0; i < newarr.length; i++) {
        if (newarr[i].delete == 1) {
          lastarry.push(newarr[i]);
        }
      }
      lastarry.forEach((ele) => {
        ele.PA = PA;
        ele.BC = BC;
        ele.GUID = uuids;
        ele.ItemNumber = ItemNumber;
        ele.Time = time;
        if (ele.FixedLoss == undefined) {
          ele.FixedLoss = 0;
        }
        ele.TotalDemand = parseInt(TaiShu * ele.AmountUsed + ele.FixedLoss);
      });

      for (let i = 0; i < lastarry.length; i++) {
        // 库存表的情况
        const sql = `select WarehouseName ,ShippingSpace,BatchNumber,cTransferQuantity from inventorytable_copy1 where MaterialCode = '${lastarry[i].MaterialCode}'`;
        const data = await mydb(sql);
        // 在途表
        const sql1 = `select WarehouseName from transit_list_copy1 where MaterialCode = '${lastarry[i].MaterialCode}'`;
        const data1 = await mydb(sql1);
        // 委外表
        const sqlw = `select WarehouseName from veneer_copy1 where MaterialCode = '${lastarry[i].MaterialCode}'`;
        const dataw = await mydb(sqlw);
        if (data.length > 0) {
          lastarry[i].state = "仓库调拨";
          lastarry[i].WarehouseName = data.WarehouseName;
        } else if (data1 > 0 || dataw.length > 0) {
          lastarry[i].state = "在途";
        } else {
          lastarry[i].state = "待下单";
        }
        const sql2 = `INSERT INTO orderrequest_copy
        (SerialNumber,
          Time,
          BC,
          PA,
          ItemNumber,
          MaterialType,
          MaterialCode,
          MaterialName,
          SpecificationsModels,
          AmountUsed,
          Unit,
          FixedLoss,
          TotalDemand,
          GUID,
          Remark,
          state) 
        VALUES (${lastarry[i].SerialNumber},
          '${lastarry[i].Time}',
          '${lastarry[i].BC}',
          '${lastarry[i].PA}',
          '${lastarry[i].ItemNumber}',
          '${lastarry[i].MaterialType}',
          '${lastarry[i].MaterialCode}',
          '${lastarry[i].MaterialName}',
          '${lastarry[i].SpecificationsModels}',
          '${lastarry[i].AmountUsed}',
          '${lastarry[i].Unit}',
          '${lastarry[i].FixedLoss}',
          '${lastarry[i].TotalDemand}',
          '${lastarry[i].GUID}',
          '${lastarry[i].Remark}',
          '${lastarry[i].state}')`;
        await mydb(sql2);
      }

      res.send({
        GUID: uuids,
      });
    } catch (error) {}
  }
  // 重新查找录入的物料信息
  async FindMaterial(req, res, next) {
    const { GUID, MaterialCode } = req.query;
    if (MaterialCode) {
      const sql1 = `select * from orderrequest_copy where GUID= '${GUID}' and MaterialCode  = '${MaterialCode}'`;
      const data1 = await mydb(sql1);
      res.send({
        data: data1,
      });
    } else {
      const sql = `select * from orderrequest_copy where GUID= '${GUID}'`;
      const data = await mydb(sql);
      res.send({
        data: data,
      });
    }
  }
  // 查找出库存中对应的物料清单
  async Repertory(req, res, next) {
    const { MaterialCode } = req.query;
    try {
      const sql = `select ID,SerialNumber, WarehouseName ,ShippingSpace,BatchNumber,cTransferQuantity,Stockholder,Stock from inventorytable_copy1 where MaterialCode = '${MaterialCode}'`;
      const data = await mydb(sql);
      // 在途表
      const sql1 = `select ID,SerialNumber, WarehouseName ,ShippingSpace,BatchNumber,cTransferQuantity,Stockholder,Stock from transit_list_copy1 where MaterialCode = '${MaterialCode}'`;
      const data1 = await mydb(sql1);
      // 委外表
      const sqlw = `select ID,SerialNumber, WarehouseName ,ShippingSpace,BatchNumber,cTransferQuantity,Stockholder,Stock from veneer_copy1 where MaterialCode = '${MaterialCode}'`;
      const dataw = await mydb(sqlw);
      res.send({
        data1: data,
        data2: data1,
        data3: dataw,
      });
    } catch (error) {
      console.log(error);
    }
  }
  // 对查询出来的订单进行更新
  async updateRepertory(req, res, next) {
    const {
      ID1,
      ID2,
      MaterialCode,
      button,
      cTransferQuantity,
      Stock,
      TransferQuantity,
      WarehouseName,
      ShippingSpace,
      BatchNumber,
      state,
      TotalDemand,
    } = req.body;

    try {
      if (button == 1) {
        const sql = `update inventorytable_copy1 set cTransferQuantity = ${cTransferQuantity},Stock=${Stock} where ID = ${ID1} AND  MaterialCode = '${MaterialCode}' `;
        await mydb(sql);
        const sql1 = `update orderrequest_copy set TransferQuantity='${TransferQuantity}',WarehouseName='${WarehouseName}',ShippingSpace='${ShippingSpace}',BatchNumber='${BatchNumber}',state = '已调拨' where ID = ${ID2} AND MaterialCode = '${MaterialCode}' `;
        await mydb(sql1);
        res.send({
          code: "200",
        });
      } else if (button == 0) {
        let NumberOrders = Number(TotalDemand) - Number(TransferQuantity);
        const sql1 = `update orderrequest_copy set TransferQuantity='${TransferQuantity}', NumberOrders='${NumberOrders}',state = '已下单' where ID = ${ID2} AND MaterialCode = '${MaterialCode}' `;
        await mydb(sql1);
        res.send({
          code: "200",
        });
      } else {
        const sql = `update inventorytable_copy1 set cTransferQuantity = ${cTransferQuantity},Stock=${Stock} where ID = ${ID1} AND  MaterialCode = '${MaterialCode}' `;
        await mydb(sql);
        const sql1 = `update orderrequest_copy set TransferQuantity='${TransferQuantity}',WarehouseName='${WarehouseName}',ShippingSpace='${ShippingSpace}',BatchNumber='${BatchNumber}',state = '已调拨与下单' where ID = ${ID2} AND MaterialCode = '${MaterialCode}' `;
        await mydb(sql1);
        let NumberOrders = Number(TotalDemand) - Number(TransferQuantity);
        const sql2 = `update orderrequest_copy set TransferQuantity='${TransferQuantity}', NumberOrders='${NumberOrders}',state = '已调拨与下单' where ID = ${ID2} AND MaterialCode = '${MaterialCode}' `;
        await mydb(sql2);
      }
    } catch (error) {
      console.log(error);
    }
  }
}
module.exports = new FontionModule();
